package com.tfs.control;

import com.tfs.entity.CompanyPaymentByPeriod;

import javax.ejb.Stateless;
import java.sql.*;
import java.util.Date;
import java.util.List;

/**
 * User: chyvok
 * Date: 7/19/11
 * Time: 1:47 PM
 */

@Stateless
public class CompanyPaymentByPeriodControl {

    public List<CompanyPaymentByPeriod> listCompanyPaymentByPeriod(Date date1, Date date2) {

        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;

        try{
            conn = DriverManager.getConnection("jdbc:mysql://192.168.90.200:3306/tdnnfin", "root", "root");
            System.out.println("Connection established successfully.");

            String dateCondition = "";
            String query = "";

            if(date1!=null){
                dateCondition=dateCondition+" WHERE PDATE >='"+date1.toString()+"'";
            }
            if(date2!=null){
                if(dateCondition.equals("")){
                    dateCondition=dateCondition+" WHERE PDATE <='"+date2.toString()+"'";
                } else {
                    dateCondition=dateCondition+" AND PDATE <='"+date2.toString()+"'";
                }
            }

            query=query+"SELECT PTABLE.COMPANYID,COMPANIES.NAME," +
                        "COMPANIES.GROUPID,COMPANYGROUPS.NAME AS GNAME,SUM(IF(BANK=1,AMOUNT,0)) AS BAMOUNT," +
                        "SUM(IF(BANK=0,AMOUNT,0)) AS CAMOUNT " +
                        "FROM PAYMENTSS "+dateCondition+" AS PTABLE " +
                        "LEFT JOIN COMPANIES ON PTABLE.COMPANYID=COMPANIES.COMPANYID" +
                        "LEFT JOIN COMPANYGROUPS ON COMPANIES.GROUPID=COMPANYGROUPS.GROUPID"+
                        "GROUP BY COMPANYID ORDER BY GROUPID,COMPANYID;";


            stmt=conn.createStatement();

            rs=stmt.executeQuery(query);
            while (rs.next()) {
                CompanyPaymentByPeriod cpbp = new CompanyPaymentByPeriod();
                cpbp.setCompanyName("");
                cpbp.setBankPayment(rs.getFloat("BAMOUNT"));
                cpbp.setCashPayment(rs.getFloat("CAMOUNT"));

            }

        } catch (SQLException se) {
            System.out.println("SQL error: "+se.getMessage()+"\n\t code"+se.getErrorCode());
        } catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }

        return null;
    }
}
